﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.IO;

namespace LarasMutiaraDiva.Skripsi.Libs
{
    static class DataBaseHelper
    {
        private static string connectionString = ConfigurationManager.AppSettings["connectionString"];
        public static string korpusDirectory = ConfigurationManager.AppSettings["corpusLocation"];

        public static void TestSqlServerConnection()
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();
                connection.Close();
                Console.WriteLine("Connection success");
            }
            catch (Exception error)
            {
                Console.WriteLine("Connection failed");
                Console.WriteLine("error: " + error.Message);
            }
        }

        public static string StemmingWord(string word)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            var sql = "SELECT dbo.isStopword('" + word + "') as term"; //command string --> if isStopWord then return "0" else return stemmed word
            SqlCommand command = new SqlCommand(sql, connection);
            
            SqlDataReader reader = command.ExecuteReader(); //reader command
            reader.Read();
            var term = reader["term"].ToString();   //get sql result
            connection.Close();
            reader.Close();

            return term;                //return stemmed word
        }
        /*
         * Seharusnya ini UPDATE aja, jangan insert
         * 
         */
        public static void InsertDocLength()
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            var fileNames = CassandraApp.GetFileInDirectory(korpusDirectory);
            foreach(var fileName in fileNames){
                SqlCommand sqlCommand = new SqlCommand("INSERT INTO Table_Doc_Length_Cass(doc_name, doc_length) VALUES('" + Path.GetFileName(fileName) + "', '"+TriftUtility.GetDocLength(Path.GetFileName(fileName))+"')", connection);
                sqlCommand.ExecuteNonQuery();
            }
            connection.Close();
            
        }

        public static bool AddNewDocument(string newDocName, out string errorMessage)
        {

            bool status = false;
            try
            {
                using (var dataContext= new KBBIModelDataContext(connectionString))
                {
                        //periksa apakah newDocName sudah ada
                    var item = dataContext.Table_Doc_Length_Casses.Where(
                        i => i.doc_name.Equals(newDocName)
                        ).SingleOrDefault();

                    if(item==null)      //doc belum ada
                    {
                        var newItem = new Table_Doc_Length_Cass()
                                       {
                                           doc_name = newDocName
                                       };
                        dataContext.Table_Doc_Length_Casses.InsertOnSubmit(newItem);
                        dataContext.SubmitChanges();

                        status = true;
                        errorMessage = string.Empty;
                    }
                    else                //doc sudah ada
                    {
                        errorMessage = "Dokumen sudah ada sebelumnya";
                    }

                }
                
            }catch(Exception error)
            {
                errorMessage = error.Message;
            }
            return status;

        }

        public static bool UpdateDocLength()
        {
            var status = false;
            using (var dataContext = new KBBIModelDataContext(connectionString))
            {
                dataContext.ObjectTrackingEnabled = true;
                var items = dataContext.Table_Doc_Length_Casses;
               
                foreach (var item in items)
                {
                    Console.WriteLine(item.doc_name);
                    var docLength = TriftUtility.GetDocLength(item.doc_name);
                    item.doc_length = docLength;
                    dataContext.SubmitChanges();
                }
                status = true;
            }

            return status;
        }

        public static double GetDocLength(string doc)
        {
            

            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT doc_length FROM Table_Doc_Length_Cass WHERE doc_name ='" + doc + "'", connection);
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            return Convert.ToDouble(reader[0]);
        }

        public static List<string> GetAllDocuments()
        {
            using(var dataContext=new KBBIModelDataContext(connectionString))
            {
                var docs = from i in dataContext.Table_Doc_Length_Casses
                           select i.doc_name;
                return docs.ToList();
            }
        }


        public static List<string> GetSortedDoc()
        {
            var kueri = Console.ReadLine();
            var item = CassandraApp.getTermKueri(kueri);
            Dictionary<string, double> sortedSimDoc = new Dictionary<string, double>();
            List<string> sortedDoc = new List<string>();

            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            var fileNames = CassandraApp.GetFileInDirectory(korpusDirectory);
            foreach (var fileName in fileNames)
            {
                var simDoc =  CassandraApp.GetSimilarity(item, fileName);
                sortedSimDoc.Add(fileName, simDoc);
            }
            var items = from k in sortedSimDoc.Keys
                        orderby sortedSimDoc[k] descending
                        select k;
            foreach (var word in items)
            {
                sortedDoc.Add(word);
            }
            return sortedDoc;
        }
    }
}
